﻿using Dapper;
using Entity;
using IRepository;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Repository
{
    public class SalesOrderRepository : ISalesOrderRepository
    {

        private readonly string _connectionString;

        public SalesOrderRepository (IConfiguration configuration)
        {
            // 从配置中读取连接字符串
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        public async Task<List<ProductInformationEntity2>> GetAllProductInformationAsync(string product_code = null, string product_name = null, string unit = null)
        {
            string productNameParam = string.IsNullOrEmpty(product_name) ? null : $"%{product_name}%";

            string sql = @"
SELECT
    bpi.product_code AS product_code,
    bpi.product_name AS product_name,
    bpi.specification_model AS specification_model,
    d.DictLabel AS unit,
    bpi.stkInrefp AS stkInrefp,
    SUM(ki.current_inventory) AS current_inventory, -- 汇总所有库位的库存
    tog.product_type_name AS product_type_name
FROM
    dbo.wms_basic_product_information bpi
INNER JOIN dbo.wms_knzy_inventory ki 
    ON bpi.product_code = ki.product_code
INNER JOIN dbo.wms_basic_type_of_goods tog 
    ON bpi.product_type_id = tog.product_type_id
INNER JOIN dbo.wms_Dictionary_data d 
    ON bpi.unit = d.DictValue AND d.DictTypeCode = 'Unit'
WHERE
    bpi.is_delete = 0
    AND (
      (@product_code IS NULL OR @product_code = '' OR bpi.product_code = @product_code)
      OR
      (@product_name IS NULL OR @product_name = '' OR bpi.product_name LIKE @product_name)
    )
    AND (@unit IS NULL OR @unit = '' OR d.DictLabel = @unit)
GROUP BY
    bpi.product_code,
    bpi.product_name,
    bpi.specification_model,
    d.DictLabel,
    bpi.stkInrefp,
    tog.product_type_name;";

            using (var conn = new SqlConnection(_connectionString))
            {
                await conn.OpenAsync();
                var param = new
                {
                    product_code,
                    product_name = productNameParam,
                    unit
                };
                return (await conn.QueryAsync<ProductInformationEntity2>(sql, param)).AsList();
            }
        }

        public async Task<List<SysOrgUserManagement>> GetAllPersonInChargeAsync()
        {
            var userList = new List<SysOrgUserManagement>();
            var sql = @"
        SELECT user_id, user_name, organization_id, status, is_delete 
        FROM sys_org_user_management 
        WHERE status = '1'  
          AND is_delete = '0'  
          -- 可根据业务需求添加岗位筛选，例如：job_id = 销售岗位ID
    ";

            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(sql, connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            userList.Add(new SysOrgUserManagement
                            {
                                UserId = reader.GetInt32(reader.GetOrdinal("user_id")),
                                UserName = reader.GetString(reader.GetOrdinal("user_name")),
                                OrganizationId = reader.GetInt32(reader.GetOrdinal("organization_id")),
                                Status = reader.GetString(reader.GetOrdinal("status")),
                                IsDelete = reader.GetString(reader.GetOrdinal("is_delete"))
                            });
                        }
                    }
                }
            }
            return userList;
        }

        public async Task<WMSBasicCustomer> GetCustomerDetailById(int customerId)
        {
            WMSBasicCustomer customer = null;
            string sql = @"
        SELECT contact_person AS ContactPerson, 
               contact_phone_number AS ContactPhoneNumber
        FROM wms_basic_customer
        WHERE customer_id = @CustomerId 
          AND is_delete = '0' 
          AND status = '1';";

            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(sql, connection))
                {
                    // 添加参数，防止SQL注入
                    command.Parameters.AddWithValue("@CustomerId", customerId);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            customer = new WMSBasicCustomer
                            {
                                contact_person = reader.GetString(reader.GetOrdinal("ContactPerson")),
                                contact_phone_number = reader.IsDBNull(reader.GetOrdinal("ContactPhoneNumber"))
                                    ? ""
                                    : reader.GetString(reader.GetOrdinal("ContactPhoneNumber"))
                            };
                        }
                    }
                }
            }
            return customer;
        }

        // 实现异步方法（关键修正）
        public async Task<List<CustomerSelectDto>> GetCustomerSelectList()
        {
            var result = new List<CustomerSelectDto>(); // 定义结果集合

            string sql = @"
                SELECT 
                    customer_id AS Value,
                    customer_name AS Label
                FROM 
                    wms_basic_customer
                WHERE 
                    is_delete = '0' 
                    AND status = '1';";

            // 异步打开连接（使用await）
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync(); // 异步打开

                using (var command = new SqlCommand(sql, connection))
                {
                    // 异步执行查询
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        // 异步读取数据
                        while (await reader.ReadAsync())
                        {
                            result.Add(new CustomerSelectDto
                            {
                                Value = reader.GetInt32(reader.GetOrdinal("Value")),
                                Label = reader.GetString(reader.GetOrdinal("Label"))
                            });
                        }
                    }
                }
            }

            return result;
        }

        public async Task<SysOrgOrganizations> GetOrganizationByIdAsync(int? orgId)
        {
            SysOrgOrganizations dept = null;
            var sql = @"
    SELECT organization_id, organization_name, status, is_delete 
    FROM sys_org_organizations 
    WHERE organization_id = @OrgId 
      AND status = '1'  
      AND is_delete = '0'  
";

            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("@OrgId", SqlDbType.Int) { Value = orgId });

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            dept = new SysOrgOrganizations
                            {
                                organization_id = reader.GetInt32(reader.GetOrdinal("organization_id")),
                                organization_name = reader.GetString(reader.GetOrdinal("organization_name")),
                                status = reader.GetString(reader.GetOrdinal("status")),
                                is_delete = reader.GetString(reader.GetOrdinal("is_delete"))
                            };
                        }
                    }
                }
            }
            return dept;
        }

        public async Task<List<OrganizationTreeDto>> GetOrganizationTreeAsync()
        {
            List<OrganizationTreeDto> result = new List<OrganizationTreeDto>();
            string sql = @"
        SELECT 
            organization_id, 
            organization_name, 
            parent_organization_id
        FROM 
            sys_org_organizations
        WHERE 
            is_delete = '0' 
            AND status = '1'
        ORDER BY 
            sorting;";

            using (var connection = new SqlConnection(_connectionString))
            {
                // 异步打开连接
                await connection.OpenAsync();

                using (var command = new SqlCommand(sql, connection))
                {
                    // 异步执行查询，获取数据读取器
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        var allOrgs = new List<OrganizationTreeDto>();
                        // 异步读取数据
                        while (await reader.ReadAsync())
                        {
                            allOrgs.Add(new OrganizationTreeDto
                            {
                                OrganizationId = reader.GetInt32(reader.GetOrdinal("organization_id")),
                                OrganizationName = reader.GetString(reader.GetOrdinal("organization_name")),
                                ParentOrganizationId = reader.IsDBNull(reader.GetOrdinal("parent_organization_id"))
                                    ? 0
                                    : reader.GetInt32(reader.GetOrdinal("parent_organization_id"))
                            });
                        }

                        // 构建树形结构（递归逻辑不变，因为内存操作无需异步）
                        result = allOrgs.Where(o => o.ParentOrganizationId == 0).ToList();
                        foreach (var org in result)
                        {
                            BuildTree(org, allOrgs);
                        }
                    }
                }
            }
            return result;
        }

        public async Task<List<OrganizationTreeDto>> GetOrganizationTreeAsync2(string keyword)
        {
            List<OrganizationTreeDto> result = new List<OrganizationTreeDto>();
            var sql = @"
        SELECT 
            organization_id, 
            organization_name, 
            parent_organization_id
        FROM 
            sys_org_organizations
        WHERE 
            is_delete = '0' 
            AND status = '1'
            AND (@Keyword = '' OR organization_name LIKE '%' + @Keyword + '%')  -- 模糊查询条件
        ORDER BY 
            sorting;";

            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Keyword", keyword ?? ""); // 处理空值
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        var allOrgs = new List<OrganizationTreeDto>();
                        while (await reader.ReadAsync())
                        {
                            allOrgs.Add(new OrganizationTreeDto
                            {
                                OrganizationId = reader.GetInt32(reader.GetOrdinal("organization_id")),
                                OrganizationName = reader.GetString(reader.GetOrdinal("organization_name")),
                                ParentOrganizationId = reader.IsDBNull(reader.GetOrdinal("parent_organization_id"))
                                    ? 0
                                    : reader.GetInt32(reader.GetOrdinal("parent_organization_id"))
                            });
                        }
                        //// 构建树形结构（原有逻辑不变）
                        //result = allOrgs.Where(o => o.ParentOrganizationId == 0).ToList();
                        //foreach (var org in result)
                        //{
                        //    BuildTree(org, allOrgs);
                        //}
                        // 【关键修改】：移除根节点筛选，保留所有查询到的节点
                        result = allOrgs;
                        foreach (var org in result)
                        {
                            BuildTree(org, allOrgs);
                        }
                        // 新增：打印 allOrgs 数量
                        Console.WriteLine($"SQL 查询返回的部门数量：{allOrgs.Count}");
                    }
                }
            }
            return result;
        }

        public async Task<SysOrgUserManagement> GetUserByIdAsync(int userId)
        {
            SysOrgUserManagement user = null;
            var sql = @"
    SELECT user_id, user_name, organization_id, status, is_delete 
    FROM sys_org_user_management 
    WHERE user_id = @UserId 
      AND status = '1'  
      AND is_delete = '0'  
";

            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int) { Value = userId });

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            user = new SysOrgUserManagement
                            {
                                UserId = reader.GetInt32(reader.GetOrdinal("user_id")),
                                UserName = reader.GetString(reader.GetOrdinal("user_name")),
                                OrganizationId = reader.GetInt32(reader.GetOrdinal("organization_id")),
                                Status = reader.GetString(reader.GetOrdinal("status")),
                                IsDelete = reader.GetString(reader.GetOrdinal("is_delete"))
                            };
                        }
                    }
                }
            }
            return user;
        }

        public async Task<List<SysOrgUserManagement>> GetUsersByDeptIdAsync(int deptId)
        {

            var userList = new List<SysOrgUserManagement>();
            // 修改列名为数据库实际命名（下划线格式）
            var sql = @"
    SELECT user_id, user_name, organization_id, status, is_delete 
    FROM sys_org_user_management 
    WHERE organization_id = @DeptId 
      AND status = '1'  
      AND is_delete = '0'  
";

            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("@DeptId", SqlDbType.Int) { Value = deptId });

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            userList.Add(new SysOrgUserManagement
                            {
                                UserId = reader.GetInt32(reader.GetOrdinal("user_id")),
                                UserName = reader.GetString(reader.GetOrdinal("user_name")),
                                OrganizationId = reader.GetInt32(reader.GetOrdinal("organization_id")),
                                Status = reader.GetString(reader.GetOrdinal("status")),
                                IsDelete = reader.GetString(reader.GetOrdinal("is_delete"))
                            });
                        }
                    }
                }
            }
            return userList;
        }



        /// <summary>
        /// 递归构建树形结构（内存操作，无需异步）
        /// </summary>
        private void BuildTree(OrganizationTreeDto parent, List<OrganizationTreeDto> allOrgs)
        {
            var children = allOrgs.Where(o => o.ParentOrganizationId == parent.OrganizationId).ToList();
            if (children.Any())
            {
                parent.Children = children;
                foreach (var child in children)
                {
                    BuildTree(child, allOrgs);
                }
            }
        }

        public async Task<int> AddSalesOrderWithDetailsAsync(WMSOrderSalesOrder order, List<WMSOrderSalesOrderDetails> detailsList)
        {
            // 主表插入SQL
            string orderSql = @"
INSERT INTO wms_order_sales_order (
    customer_id, rule_id, sales_order_no, sales_date, document_status,
    person_in_charge, product_id, reviewer, review_time, organizations_id,
    note, create_by, create_time, update_by, update_time, is_delete, annex_code
) VALUES (
    @customer_id, @rule_id, @sales_order_no, @sales_date, @document_status,
    @person_in_charge, @product_id, @reviewer, @review_time, @organizations_id,
    @note, @create_by, GETDATE(), @update_by, @update_time, @is_delete, @annex_code
);
SELECT CAST(SCOPE_IDENTITY() AS INT);";

            // 明细表插入SQL（关联主表ID）
            string detailsSql = @"
INSERT INTO wms_order_sales_order_details (
    sales_order_id, product_code, selling_unit_price, prod_num,
    purreturn_num, outbound_num, note, create_by, create_time,
    update_by, update_time, is_delete,saleamount
) VALUES (
    @sales_order_id, @product_code, @selling_unit_price, @prod_num,
    @purreturn_num, @outbound_num, @note, @create_by, @create_time,
    @update_by, @update_time, @is_delete,@saleamount
);";

            using (var conn = new SqlConnection(_connectionString))
            {
                await conn.OpenAsync();
                using (var transaction = conn.BeginTransaction()) // 同一事务
                {
                    try
                    {
                        // 1. 新增主表，获取主表ID
                        int salesOrderId = await conn.QuerySingleAsync<int>(
                            orderSql,
                            order,
                            transaction
                        );

                        // 2. 遍历所有明细，关联主表ID后批量插入
                        foreach (var detail in detailsList)
                        {
                            detail.sales_order_id = salesOrderId; // 绑定主表ID
                            await conn.ExecuteAsync(
                                detailsSql,
                                detail,
                                transaction
                            );
                        }

                        // 所有操作成功，提交事务
                        transaction.Commit();
                        return salesOrderId; // 返回主表ID
                    }
                    catch (Exception ex)
                    {
                        // 任意步骤失败，回滚所有操作
                        transaction.Rollback();
                        throw new Exception("新增订单及明细失败：" + ex.Message);
                    }
                }
            }
        }

        public async Task<List<WMSOrderSalesOrder2>> GetAllWMSOrderSalesOrder2Async(string sales_order_no = null, string document_status = null, string customer_name = null, DateTime? sales_date = null)
        {
            string sql = @"
SELECT
    so.sales_order_id AS 'sales_order_id',
    so.sales_order_no AS 'sales_order_no',
    so.document_status AS 'document_status',
    so.sales_date AS 'sales_date',
    so.note AS 'note',
    create_user.user_name AS 'user_name',
    so.create_by AS 'create_by',
    so.create_time AS 'create_time',
    so.reviewer AS 'reviewer',
    so.review_time AS 'review_time',
    c.customer_name AS 'customer_name',
    c.contact_person AS 'contact_person',
    c.contact_phone_number AS 'contact_phone_number',
    org.organization_name AS 'organization_name',
    u.user_name AS 'user_name1',
    SUM(sod.prod_num) AS 'prod_num',
    SUM(sod.saleamount) AS 'saleamount',
    STRING_AGG(pi.product_name, ',') AS 'product_name',
    create_org.organization_name AS 'department'
FROM
    dbo.wms_order_sales_order so
JOIN dbo.wms_basic_customer c ON so.customer_id = c.customer_id
JOIN dbo.sys_org_organizations org ON so.organizations_id = org.organization_id
JOIN dbo.sys_org_user_management u ON so.person_in_charge = u.user_id
JOIN dbo.wms_order_sales_order_details sod ON so.sales_order_id = sod.sales_order_id
JOIN dbo.wms_basic_product_information pi ON sod.product_code = pi.product_code
JOIN dbo.sys_org_user_management create_user ON so.create_by = create_user.user_id
JOIN dbo.sys_org_organizations create_org ON create_user.organization_id = create_org.organization_id
WHERE 1=1
AND so.is_delete = 0
    AND (@SalesOrderNo IS NULL OR so.sales_order_no LIKE @SalesOrderNo)
    AND (@DocumentStatus IS NULL OR so.document_status = @DocumentStatus)
    AND (@CustomerName IS NULL OR c.customer_name LIKE @CustomerName)
    AND (@SalesDate IS NULL OR so.sales_date = @SalesDate)
GROUP BY
    so.sales_order_id,
    so.sales_order_no,
    so.document_status,
    so.sales_date,
    so.note,
    so.create_by,
    create_user.user_name,
    so.create_time,
    so.reviewer,
    so.review_time,
    c.customer_name,
    c.contact_person,
    c.contact_phone_number,
    org.organization_name,
    u.user_name,
    create_org.organization_name
ORDER BY so.create_time DESC";

            var parameters = new Dictionary<string, object>
    {
        { "@SalesOrderNo", string.IsNullOrEmpty(sales_order_no) ? null : (object)$"%{sales_order_no}%" },
        { "@DocumentStatus", document_status },
        { "@CustomerName", string.IsNullOrEmpty(customer_name) ? null : (object)$"%{customer_name}%" },
        { "@SalesDate", sales_date }
    };

            // 异步执行查询的正确方式：用 await 等待异步操作完成
            using (var connection = new SqlConnection(_connectionString))
            {
                // 打开连接也建议异步（避免阻塞）
                await connection.OpenAsync();

                // 等待 QueryAsync 完成，直接获取结果（无需 ContinueWith）
                var result = await connection.QueryAsync<WMSOrderSalesOrder2>(sql, parameters);

                // 转换为 List 并返回（同步操作，无需 await）
                return result.ToList();
            }
        }

        public async Task<WMSOrderSalesOrder2> GetSalesOrderDetailByNo(string saleOrderNo)
        {
            string sql = @"
                SELECT
                    so.sales_order_no AS 'sales_order_no',
                    so.document_status AS 'document_status',
                    so.sales_date AS 'sales_date',
                    so.note AS 'note',
                    create_user.user_name AS 'user_name',
                    so.create_by AS 'create_by',
                    so.create_time AS 'create_time',
                    so.reviewer AS 'reviewer',
                    so.review_time AS 'review_time',
                    c.customer_name AS 'customer_name',
                    c.contact_person AS 'contact_person',
                    c.contact_phone_number AS 'contact_phone_number',
                    org.organization_name AS 'organization_name',
                    u.user_name AS 'user_name1',
                    SUM(sod.prod_num) AS 'prod_num',
                    SUM(sod.saleamount) AS 'saleamount',
                    STRING_AGG(pi.product_name, ',') AS 'product_name',
                    create_org.organization_name AS 'department'
                FROM
                    dbo.wms_order_sales_order so
                JOIN dbo.wms_basic_customer c ON so.customer_id = c.customer_id
                JOIN dbo.sys_org_organizations org ON so.organizations_id = org.organization_id
                JOIN dbo.sys_org_user_management u ON so.person_in_charge = u.user_id
                JOIN dbo.wms_order_sales_order_details sod ON so.sales_order_id = sod.sales_order_id
                JOIN dbo.wms_basic_product_information pi ON sod.product_code = pi.product_code
                JOIN dbo.sys_org_user_management create_user ON so.create_by = create_user.user_id
                JOIN dbo.sys_org_organizations create_org ON create_user.organization_id = create_org.organization_id
                WHERE so.sales_order_no = @SaleOrderNo
                GROUP BY
                    so.sales_order_no,
                    so.document_status,
                    so.sales_date,
                    so.note,
                    so.create_by,
                    create_user.user_name,
                    so.create_time,
                    so.reviewer,
                    so.review_time,
                    c.customer_name,
                    c.contact_person,
                    c.contact_phone_number,
                    org.organization_name,
                    u.user_name,
                    create_org.organization_name";


            // 使用Dapper执行查询
            using (SqlConnection db = new SqlConnection(_connectionString))
            {
                // 异步查询单条记录（如果不存在返回null）
                var result = await db.QueryFirstOrDefaultAsync<WMSOrderSalesOrder2>(
                    sql,
                    new { SaleOrderNo = saleOrderNo } // 参数化查询，防止SQL注入
                );
                return result;
            }
        }

        public async Task<int> LogicalDeleteBatch(List<string> saleOrderNos)
        {
            using IDbConnection db = new SqlConnection(_connectionString);
            string sql = @"
            UPDATE dbo.wms_order_sales_order
            SET is_delete = 1
            WHERE sales_order_no IN @SaleOrderNos
        ";
            // 返回受影响的行数
            return await db.ExecuteAsync(sql, new { SaleOrderNos = saleOrderNos });
        }

        public async Task<int> LogicalDeleteSingle(string saleOrderNo)
        {
            using IDbConnection db = new SqlConnection(_connectionString);
            string sql = @"
            UPDATE dbo.wms_order_sales_order
            SET is_delete = 1
            WHERE sales_order_no = @SaleOrderNo
        ";
            // 返回受影响的行数
            return await db.ExecuteAsync(sql, new { SaleOrderNo = saleOrderNo });
        }

        /// <summary>
        /// 异步批量审核订单（Dapper 实现）
        /// </summary>
        public async Task<int> BatchAuditAsync(List<int> orderIds, string auditResult, string auditOpinion, string reviewer)
        {
            var sql = @"UPDATE wms_order_sales_order 
                SET audit_result = @AuditResult, 
                    audit_opinion = @AuditOpinion, 
                    document_status = CASE WHEN @AuditResult = '1' THEN '6' ELSE '3' END,
                    reviewer = @Reviewer,
                    review_time = GETDATE()
                WHERE sales_order_id IN @OrderIds  
                  AND document_status = '2'";

            using (var connection = new SqlConnection(_connectionString))
            {
                return await connection.ExecuteAsync(sql, new
                {
                    AuditResult = auditResult,
                    AuditOpinion = auditOpinion,
                    Reviewer = reviewer,
                    OrderIds = orderIds
                });
            }
        }

        /// <summary>
        /// 异步批量撤销订单（Dapper 实现）
        /// </summary>
        public async Task<int> BatchRevokeAsync(List<int> orderIds)
        {
            var sql = @"UPDATE wms_order_sales_order 
                        SET document_status = '2',
                            audit_result = NULL,
                            audit_opinion = NULL,
                            reviewer = NULL,
                            review_time = NULL
                        WHERE sales_order_id IN @OrderIds 
                          AND (document_status = '6' OR document_status = '3')";

            using (var connection = new SqlConnection(_connectionString))
            {
              return  await connection.ExecuteAsync(sql, new { OrderIds = orderIds });
            }
        }

        /// <summary>
        /// 异步校验可审核的订单ID（Dapper 实现）
        /// </summary>
        public async Task<List<int>> GetValidAuditOrderIdsAsync(List<int> orderIds)
        {
            var sql = @"SELECT sales_order_id 
                FROM wms_order_sales_order 
                WHERE sales_order_id IN @OrderIds 
                  AND document_status = '2'";

            using (var connection = new SqlConnection(_connectionString))
            {
                return (await connection.QueryAsync<int>(sql, new { OrderIds = orderIds }))
                    .ToList();
            }
        }

        /// <summary>
        /// 异步校验可撤销的订单ID（Dapper 实现）
        /// </summary>
        public async Task<List<int>> GetValidRevokeOrderIdsAsync(List<int> orderIds)
        {
            var sql = @"SELECT sales_order_id 
                        FROM wms_order_sales_order 
                        WHERE sales_order_id IN @OrderIds 
                          AND (document_status = '6' OR document_status = '3')";

            using (var connection = new SqlConnection(_connectionString))
            {
                return (await connection.QueryAsync<int>(sql, new { OrderIds = orderIds }))
                    .ToList();
            }
        }

        public async Task<int> LogicalRevokeSingle(int saleOrderId)
        {
            var sql = @"UPDATE wms_order_sales_order 
                SET document_status = '2',
                    audit_result = NULL,
                    audit_opinion = NULL,
                    reviewer = NULL,
                    review_time = NULL
                WHERE sales_order_id = @SaleOrderId 
                  AND (document_status = '6' OR document_status = '3')";

            using (var connection = new SqlConnection(_connectionString))
            {
                return await connection.ExecuteAsync(sql, new { SaleOrderId = saleOrderId });
            }
        }

        public async Task<IEnumerable<dynamic>> GetUsersByOrgIdAsync(int orgId, string keyword = "")
        {
            // SQL语句写在数据访问层
            var sql = @"SELECT 
                        user_id AS UserId,  -- 别名映射，与前端字段对应
                        user_name AS UserName 
                    FROM 
                        sys_org_user_management 
                    WHERE 
                        organization_id = @OrgId 
                        AND is_delete = '0'  -- 未删除
                        AND status = '1'     -- 状态正常
                        AND (@Keyword = '' OR user_name LIKE @Keyword)"; 

        using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                return await connection.QueryAsync<dynamic>(
                    sql,
                    new
                    {
                        OrgId = orgId,
                        Keyword = $"%{keyword}%"  // 模糊查询拼接
                    }
                );
            }
        }
    }
}
